Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Tuning the Application

When analyzing the SQL statement, you should look for two things:

  The SQL statement. What does it do?
  The effect of the SQL statement. What is it doing it to? How does it fit into the big picture?

By looking at the SQL statement from these different angles, you may find a problem that you wouldn’t find by just looking at it from one viewpoint.

For example, consider an application that does not used cached sequences to generate a primary key value. By itself, there is nothing wrong with this approach and the application is probably very efficient. But add a thousand users executing the same application and the problem is quite apparent: You have contention getting the value for the primary key value.

The SQL Statement

The best way to go about tuning the SQL statements of an existing application is to follow these few steps:

1.  Familiarize yourself with the application. You should be familiar not only with the specific SQL statements but with the purpose of the application and what it does.
2.  Use the SQL Trace facility to analyze what the particular SQL statements are doing, what features of the RDBMS are being used, and how well those features are being used.
3.  Use EXPLAIN PLAN within SQL Trace to analyze how the optimizer is executing those SQL statements.

Now take a look at some specifics of these steps.

Familiarize Yourself with the Application

Look not only at the SQL statements themselves but at the effect of those statements. Make a chart of the different SQL statements and determine the number of accesses each SQL statement makes to each table in the database. This chart can give you an effective visual idea of which tables are being accessed most frequently. Consider the example shown in Figure 26.1.


Figure 26.1  An example of an SQL statement-analysis chart.

This chart is a good quick reference for which SQL statements are affecting which tables. You can take this a step further and split the chart into different types of statements such as SELECTs, INSERTs, UPDATEs, DELETEs, and so on. Depending on your system and whether your application is shrink-wrapped or developed in-house, this may be or may not be practical.

Use SQL Trace To Analyze the SQL Statements

By running SQL Trace on the SQL statements, you can gather much valuable information about the specific operation of each of the SQL statements. SQL Trace provides such valuable information as the following:

  Parse, execute, and fetch counts
  CPU and elapsed times
  Physical and logical reads
  Number of rows processed
  Library cache misses

You can use this information to determine which SQL statements are efficient and which ones are not. Look for the following indications of inefficient statements:


SQL Trace Output Comments

CPU and elapsed time If the CPU or elapsed times are very high, this SQL statement is a good candidate for tuning. It doesn’t make much sense to spend time tuning statements that don’t use many resources.
Executes Focus on SQL statements that are frequently executed. Don’t spend time on SQL statements that are infrequently used.
Rows Processed An SQL statement with a high number of rows processed may not be using an index effectively.
Library Cache A large number of library cache misses may indicate a need to tune the shared pool or to change the SQL statement to take advantage of the shared SQL area.

These clues may point you in the direction of the SQL statements that need to be tuned. You may have to alter these SQL statements to improve their efficiency. By using EXPLAIN PLAN, you may find addition areas that can be improved. For more information about SQL Trace, refer to Chapter 25, “Using EXPLAIN PLAN and SQL Trace.”

Use EXPLAIN PLAN To Analyze Statement Execution

By running EXPLAIN PLAN as part of the SQL Trace report, you can get a better idea of how the SQL statement is actually being executed by Oracle. This information (and the information supplied by SQL Trace) helps you judge the efficiency of the SQL statement. Here is a list of some of the things to look for:

  Are the table’s indexes being used when they should be? If not, the statement may not be supplying the correct parameters in the WHERE clause.
  Are indexes being used when they should not be? In cases when you are selecting too much data, you may want to use the FULL hint to bypass the index.
  What is the cost of the SQL statement? This value is given in the position column of the first row of the table returned by EXPLAIN PLAN.
  What is the amount of overhead incurred from SELECT or UPDATE operations?
  Is the statement being parallelized? You may have to provide a hint to effectively take advantage of the Parallel Query option.

You should ask these questions and your own specific questions as you review the EXPLAIN PLAN output. By knowing what your application is supposed to do, you may find important information about the efficiency of your statements by looking at this information. For more about the use of EXPLAIN PLAN, refer to Chapter 25.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.